package com.xiguaji.tiktok.v1.ins.brand.repository.impl;

import com.xiguaji.boot.toolkit.jdbc.JdbcTemplateHelper;
import com.xiguaji.boot.toolkit.jdbc.Page;
import com.xiguaji.tiktok.v1.ins.brand.dto.*;
import com.xiguaji.tiktok.v1.ins.brand.repository.CommonBrandExtRepository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

public class CommonBrandExtRepositoryImpl implements CommonBrandExtRepository {

    @Override
    public Page<MediaInfoResp> getBrandMediaPage(BrandMediaSearchRes brandMediaSearchRes,long startTime,long endTime) {
        String column = "m.id,m.mid,m.taken_time,m.shortcode,m.score,m.like_count likes,m.comment_count reviews,m.er,m.display_url,m.is_sponsored,m.is_video,m.product_type,b.username,b.full_name,b.uid,b.pic_url";
        String join = "INNER JOIN ins_media m ON rbm.shortcode = m.shortcode LEFT JOIN ins_blogger b ON rbm.owner_uid = b.uid ";
        StringBuilder sql = new StringBuilder("select {column} ");
        sql.append (" from ins_rel_brand_media rbm ");
        sql.append("{join}");
        sql.append("where rbm.bid = ? ");
        sql.append("and rbm.taken_time between ? and ? ");
        List<Object> params = new ArrayList<>();

        if(brandMediaSearchRes.getUid() > 0){
            sql.append("and rbm.owner_uid = ? ");
            params.add(brandMediaSearchRes.getUid());
        }
        params.add(brandMediaSearchRes.getBrandId());
        params.add(startTime);
        params.add(endTime);
        String and = " and ";

        switch (brandMediaSearchRes.getMediaType()) {
            case 1:
                sql.append(and).append(" rbm.is_video = 0 ");
                break;
            case 2:
                sql.append(and).append(" rbm.product_type = 'feed' and rbm.is_video = 1 ");
                break;
            case 3:
                sql.append(and).append(" rbm.product_type = 'clips' and rbm.is_video = 1 ");
                break;
            case 4:
                sql.append(and).append(" rbm.product_type = 'igtv' and rbm.is_video = 1 ");
                break;
            default:
        }


        if(brandMediaSearchRes.getSponsored()== 1){
            sql.append(and +" rbm.is_sponsored = 1 ");
        }

        if(brandMediaSearchRes.getBusiness() == 1){
            sql.append(and+" rbm.is_ad = 1 ");
        }

        if(brandMediaSearchRes.getProduct() == 1){
            sql.append(and+" rbm.product_tag_type in (1,2,3) ");
        }

        int totalRow = JdbcTemplateHelper.getJdbcTemplate().
                queryForObject(sql.toString().replace("{column}","count(*)").replace("{join}",""),Integer.class,params.toArray());
        int totalPage = totalRow % brandMediaSearchRes.getPageSize() == 0 ? totalRow / brandMediaSearchRes.getPageSize() : totalRow / brandMediaSearchRes.getPageSize() + 1;



        String sortStr = " order by rbm.score desc";
        if(brandMediaSearchRes.getSort() == 1){
            sortStr = " order by rbm.taken_time desc";
        }
        sql.append(sortStr);

        sql.append(" LIMIT " + (brandMediaSearchRes.getPage() - 1) * brandMediaSearchRes.getPageSize() + "," + brandMediaSearchRes.getPageSize());

        List<MediaInfoResp> list = JdbcTemplateHelper.findEntity(MediaInfoResp.class,sql.toString().replace("{column}",column).replace("{join}",join),params.toArray());

        Page<MediaInfoResp> page = new Page<>(list,brandMediaSearchRes.getPage(),brandMediaSearchRes.getPageSize(),totalPage,totalRow);
        return page;
    }

    @Override
    public Page<ProductInfoResp> getBrandProductPage(int page, int pageSize, List<Long> uIds, String category,long startTime,long endTime){
        List<Object> params = new ArrayList<>();
        String uIdStr = "";
        for (Long uId : uIds) {
            if(!StringUtils.isEmpty(uIdStr)){
                uIdStr+= ",";
            }
            uIdStr+=uId;
        }

        String select = "select a.* ";
        StringBuilder from = new StringBuilder();
        from.append(" from (select p.product_id,p.overall_category_name category,p.product_name,p.price,p.image,count(m.id) posts,IFNULL(SUM(m.like_count+m.comment_count),0) engagements ");
        from.append("from ins_rel_media_product mp INNER JOIN ins_product p ON mp.product_id = p.product_id left join ins_media m ON mp.shortcode = m.shortcode ");
        from.append("where p.merchant_uid in ("+uIdStr+") and mp.taken_time between ? and ? ");
        // params.add(uIds);
        params.add(startTime);
        params.add(endTime);

        if (!StringUtils.isEmpty(category)){
            from.append("and p.overall_category_name = ? ");
            params.add(category);
        }
        from.append("GROUP BY p.product_id) a order by a.engagements desc,a.product_name desc");
        return JdbcTemplateHelper.paginate(ProductInfoResp.class,page,pageSize,select,from.toString(),params.toArray());
    }

    @Override
    public Page<BloggerInfoResp> getInfluencerPage(int page,int pageSize,int bid,Boolean isBrandAccount,long startTime,long endTime){
        String selectSql = "select rbm.posts,b.uid,b.pic_url,b.username,b.full_name,b.category_name,b.follower_count,b.is_verified,rbm.engagements ";
        String fromSql = "  from (select SUM(rbm.like_count+rbm.comment_count) engagements,rbm.owner_uid,count(*) posts " +
                "from ins_rel_brand_media rbm where rbm.bid = ? {} and rbm.taken_time between ? and ? GROUP BY rbm.owner_uid ) rbm " +
                "inner join  ins_blogger_main b on rbm.owner_uid = b.uid order by rbm.engagements desc,b.follower_count desc";
        List<Object> params = new ArrayList<>();
        params.add(bid);
        if(null != isBrandAccount){
            fromSql = fromSql.replace("{}","and rbm.is_brand_account = ?");
            params.add(isBrandAccount);
        }
        params.add(startTime);
        params.add(endTime);
        return JdbcTemplateHelper.paginate(BloggerInfoResp.class,page,pageSize,selectSql,fromSql,params.toArray());
    }

    /**
     * 品牌账户列表
     * @param bid
     * @param startTime
     * @param endTime
     * @param page
     * @param pageSize
     * @return
     */
    @Override
    public Page<InsBrandAccountInfo> findBrandAccountPage(int bid, long startTime, long endTime, int page, int pageSize){
        String selectSql = "select tr.* ";
        String fromSql = " from (select t1.*,ifnull(t2.post,0)post,ifnull(t2.ec,0)ec  from " +
                " (select uid,follower_count,username,commercial_category_name category,pic_url " +
                " from ins_blogger_main where uid in ( SELECT uid FROM ins_rel_brand_blogger WHERE bid = ? ))t1 " +
                " left join " +
                " (SELECT " +
                "  owner_uid, " +
                " count(1) post, " +
                " sum(like_count + comment_count) ec " +
                " FROM " +
                " ins_media  " +
                " WHERE " +
                " owner_uid IN ( SELECT uid FROM ins_rel_brand_blogger WHERE bid = ? )  " +
                " AND taken_time BETWEEN ? AND ? " +
                " group by owner_uid)t2 " +
                " on t2.owner_uid = t1.uid " +
                " order by ec desc, post desc ,follower_count desc) tr ";
        List<Object> params = new ArrayList<>();
        params.add(bid);
        params.add(bid);
        params.add(startTime);
        params.add(endTime);
        return JdbcTemplateHelper.paginate(InsBrandAccountInfo.class,page,pageSize,selectSql,fromSql,params.toArray());
    }
}
